/*******************************************************************************
 * @project: aohuaproject
 * @package: com.aohua.erp
 * @file: DBService.java
 * @author: fionn
 * @created: 2015年8月13日
 * @purpose:
 * 
 * @version: 1.0
 * 
 * 
 * Copyright 2015 HAND All rights reserved.
 ******************************************************************************/
package com.aohua.erp;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import java.util.Properties;

//import oracle.jdbc.OraclePreparedStatement;

import org.apache.log4j.Logger;

/**
 * @author fionn
 *
 */
public class DBService {

    private static Logger log = Logger.getLogger(DBService.class);
    private String dburl;
    private String user;
    private String passwd;
    private String driver;
    private Connection conn;
    
    public DBService(String configFile) {
        InputStream instream = null;
        try {
            instream = DBService.class.getResourceAsStream(configFile);
            Properties p = new Properties();
            p.load(instream);
            this.dburl = p.getProperty("db.url");
            this.user = p.getProperty("db.user");
            this.passwd = p.getProperty("db.passwd");
            this.driver = p.getProperty("db.driver");
        } catch (IOException e) {
            log.error(e.getMessage(),e);
        }finally {
            if(instream != null) {
                try {
                    instream.close();
                } catch (IOException e) {
                    log.error(e.getMessage(),e);
                }
            }
        }
    }

    /**
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public void connect() throws ClassNotFoundException, SQLException {
        Class.forName(driver);
//        DriverManager.registerDriver(new OracleDriver());
        conn = DriverManager.getConnection(dburl, user, passwd);
    }
    
    /**
     * close database connection
     */
    public void close() {
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
               log.error(e.getMessage(), e);
            }
        }
    }
    /**
     * @param payload
     */
    public void writePayload(ERPItemPayload payload) {
        PreparedStatement opstat = null;
        try {
            conn.setAutoCommit(false);
            opstat = conn.prepareStatement(getInsertERPItemPayloadSQL());
//            OraclePreparedStatement opstat = (OraclePreparedStatement)pstat;
            int i = 1;
            opstat.setString(i, payload.getEcoNumber());
            i++; opstat.setString(i, payload.getNumber());
            i++; opstat.setString(i, payload.getType());
            i++; opstat.setString(i, payload.getDescription());
            i++; opstat.setString(i, payload.getCategory());
            i++; opstat.setString(i, payload.getSpecification());
//            i++; opstat.setString(i, payload.getStatus());
            i++; opstat.setString(i, payload.getUnit());
            i++; opstat.setString(i, payload.getFeature());
            i++; opstat.setString(i, payload.getNewRev());
            i++; opstat.setString(i, payload.getOldRev());
//            i++; opstat.setString(i, payload.getNote());
            i++; opstat.setTimestamp(i, new Timestamp(new Date().getTime()));
            i++; opstat.setInt(i, 0);
            i++; opstat.setString(i, payload.getOrgName());
            
            opstat.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                log.error(e.getMessage(), e);
            }
            log.error(e.getMessage(), e);
        }finally {
            if(opstat != null) {
                try {
                    opstat.close();
                } catch (SQLException e) {
                    log.debug(e.getMessage(), e);
                }
            }
        }
    }
    
    public void writePayload(List<ERPItemPayload> payloads) {
        PreparedStatement opstat = null;
        try {
            conn.setAutoCommit(false);
            opstat = conn.prepareStatement(getInsertERPItemPayloadSQL());
//            OraclePreparedStatement opstat = (OraclePreparedStatement)pstat;
            for(ERPItemPayload payload : payloads) {
                int i = 1;
                opstat.setString(i, payload.getEcoNumber());
                i++; opstat.setString(i, payload.getNumber());
                i++; opstat.setString(i, payload.getType());
                i++; opstat.setString(i, payload.getDescription());
                i++; opstat.setString(i, payload.getCategory());
                i++; opstat.setString(i, payload.getSpecification());
//                i++; opstat.setString(i, payload.getStatus());
                i++; opstat.setString(i, payload.getUnit());
                i++; opstat.setString(i, payload.getFeature());
                i++; opstat.setString(i, payload.getNewRev());
                i++; opstat.setString(i, payload.getOldRev());
//                i++; opstat.setString(i, payload.getNote());
                i++; opstat.setTimestamp(i, new Timestamp(new Date().getTime()));
                i++; opstat.setInt(i, 0);
                i++; opstat.setString(i, payload.getOrgName());
                
                opstat.executeUpdate();
            }
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                log.error(e.getMessage(), e);
            }
            log.error(e.getMessage(), e);
        }finally {
            if(opstat != null) {
                try {
                    opstat.close();
                } catch (SQLException e) {
                    log.debug(e.getMessage(), e);
                }
            }
        }
    }
    
    public void writeBOMPayload(List<String> items, List<ERPBomPayload> payloads) {
        PreparedStatement opstat = null;
        try {
            conn.setAutoCommit(false);
            // clear BOM
            opstat = conn.prepareStatement(getClearERPBOMPayloadSQL(items));
            for(String item : items) {
                opstat.setString(1, item);
                System.out.println("Clear BOM: " + item);
                opstat.executeUpdate();
            }
            // Insert
//            PID,LEVEL,PARENT_NUMBER,FIND_NUM,ITEM_NUMBER,ITEM_PROPERTY,ISOUTSOURCING,UNIT,QTY,SC_NUMBER,MP_NUMBER,UQTY,SUB_FLAG,VERSION,EFF_DATE,REDLINE,REDLINE_NOTE
            opstat = conn.prepareStatement(getInsertERPBOMPayloadSQL());
//            OraclePreparedStatement opstat = (OraclePreparedStatement)pstat;
            for(ERPBomPayload payload : payloads) {
                int i = 1;
                opstat.setString(i, payload.getPid());
                i++; opstat.setInt(i, payload.getLevel());
                i++; opstat.setString(i, payload.getParentCode());
                i++; opstat.setString(i, payload.getParentCategory());
                i++; opstat.setInt(i, (payload.getFindNum() != null && "".equals(payload.getFindNum())) ? Integer.valueOf(payload.getFindNum()) : 0);//opstat.setString(i, payload.getFindNum());
                i++; opstat.setString(i, payload.getSubCode());
                i++; opstat.setString(i, payload.getSubItemFeature());
                i++; opstat.setString(i, payload.getSubWgjg());
                i++; opstat.setString(i, payload.getSubUnit());
                i++; opstat.setDouble(i, (payload.getSubCount() != null && "".equals(payload.getSubCount())) ? Double.valueOf(payload.getSubCount()) : 0d);//opstat.setString(i, payload.getSubCount());
                i++; opstat.setString(i, payload.getSubSCCode());
                i++; opstat.setString(i, payload.getSubMPCode());
                i++; opstat.setDouble(i,  (payload.getQty() != null && "".equals(payload.getQty())) ? Double.valueOf(payload.getQty()) : 0d);//opstat.setString(i, payload.getQty());
                i++; opstat.setString(i, payload.getSubflag());
                i++; opstat.setString(i, payload.getVersion());
                i++; opstat.setString(i, payload.getEffectivityDate());
                i++; opstat.setString(i, payload.getRedLine());
                i++; opstat.setString(i, payload.getNote());
//                i++; opstat.setTimestamp(i, new Timestamp(new Date().getTime()));
                System.out.println("Insert :" + payload.getParentCode() + " - " + payload.getSubCode());
                opstat.executeUpdate();
            }
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace(System.out);
            }
            e.printStackTrace(System.out);
        }finally {
            if(opstat != null) {
                try {
                    opstat.close();
                } catch (SQLException e) {
                    e.printStackTrace(System.out);
                }
            }
        }
    }
    
    private String getInsertERPItemPayloadSQL() {
        String sql = "INSERT INTO erp_items (ECO_NUMBER,ITEM_NUMBER,TYPE,DESCRIPTION,CATEGORY,SPECIFICATION,UNIT,FEATRUES,NEWREV,OLDREV,IN_DATE,FLAG,ORIGINAL_ITEM_NUM) " 
                + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        return sql;
    }
    
    private String getClearERPBOMPayloadSQL(List<String> items) {
        /*StringBuffer sql = new StringBuffer();
        sql.append("DELETE FROM ERP_BOMS WHERE PARENT_CODE IN (");
        for(int i = 0; i < items.size() -1; i++) {
            sql.append("'").append(items.get(i)).append("',");
        }
        sql.append("'").append(items.get(items.size() - 1)).append("'");
        sql.append(");");
        log.debug(sql);
        */
//        String sql = "DELETE FROM ERP_BOMS WHERE PARENT_NUMBER=?";
        String sql = "UPDATE ERP_BOMS SET FLAG=2 WHERE PARENT_NUMBER=?";
        return sql.toString();
    }
    
    private String getInsertERPBOMPayloadSQL() {
        String sql = "INSERT INTO erp_boms (PID,LEVEL,PARENT_NUMBER,PARENT_CATEGORY,FIND_NUM,ITEM_NUMBER,ITEM_PROPERTY,ISOUTSOURCING,UNIT,QTY,SC_NUMBER,MP_NUMBER,UQTY,SUB_FLAG,VERSION,EFF_DATE,REDLINE,REDLINE_NOTE) " 
                + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        return sql;
    }
    
}
